package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.bean.BpStoreMaterialBean;
import com.joysuch.wwyt.bp.entity.BpStoreMaterial;
import com.joysuch.wwyt.core.bean.SimpleSelectListBean;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BpStoreMaterialDao extends JpaRepository<BpStoreMaterial, Long> {

    Integer countByTypeId(Long typeId);

    Integer countByUnitId(Long typeId);

    List<BpStoreMaterial> findByHouseId(Long id);

    Double findAmountById(Long materialId);

    @Query(value = "SELECT * FROM wwyt_bp_store_material m WHERE m.delete_flag = '0'  and m.house_id =:houseId " +
            "and if(:nameOrCode !='',m.name like %:nameOrCode% or m.code like %:nameOrCode%,1=1)" +
            "and if(:typeId!='',m.type_id=:typeId,1=1)", nativeQuery = true)
    List<BpStoreMaterial> findByParams(@Param("houseId") Long houseId, @Param("nameOrCode") String nameOrCode, @Param("typeId") Long typeId);

    @Query(value = "SELECT m.*FROM wwyt_bp_store_material m left join wwyt_bp_store_house h on m.house_id=h.id" +
            " WHERE m.delete_flag = '0' and h.delete_flag = '0' and h.type_flag =:typeFlag " +
            " and if(:nameOrCode !='',m.name like %:nameOrCode% or m.code like %:nameOrCode%,1=1)" +
            " and if(:typeId!='',m.type_id=:typeId,1=1) order by m.house_id desc ,m.id desc", nativeQuery = true)
    List<BpStoreMaterial> findAppByParams(@Param("nameOrCode") String nameOrCode, @Param("typeId") Long typeId, @Param("typeFlag") Long typeFlag);

    @Query(value = "SELECT COUNT(m.id) FROM wwyt_bp_store_material m LEFT JOIN wwyt_bp_store_house h ON m.house_id = h.id WHERE m.type_id =?1 " +
            "AND h.type_flag =?2 AND h.delete_flag = '0' AND m.delete_flag = '0' ",nativeQuery = true)
    int countByTypeIdAndTypeFlag(Long id, Integer typeFlag);


    @Query(value = "SELECT COUNT(m.id) FROM wwyt_bp_store_material m LEFT JOIN wwyt_bp_store_house h ON m.house_id = h.id WHERE m.unit_id =?1 " +
            "AND h.type_flag =?2 AND h.delete_flag = '0' AND m.delete_flag = '0' ",nativeQuery = true)
    int countByUnitIdAndTypeFlag(Long id, Integer typeFlag);

    @Query(value = "SELECT * FROM wwyt_bp_store_material m WHERE m.delete_flag = '0'  and m.house_id =:houseId " +
            "and if(:nameOrCode !='',m.code like %:nameOrCode%,1=1)" +
            "and if(:chemProductId != '',m.chem_product_id = :chemProductId,1=1)" +
            "and if(:typeId!= '',m.type_id=:typeId,1=1)", nativeQuery = true)
    List<BpStoreMaterial> findchemProduct(@Param("houseId") Long id,@Param("chemProductId") Long chemProductId, @Param("nameOrCode") String nameOrCode,@Param("typeId") Long typeId);

    @Query(value = "select " +
            " distinct (chem_product_id) " +
            " from wwyt_bp_store_material m " +
            " WHERE m.delete_flag = '0' " +
            " and m.chem_product_id is not null",nativeQuery = true)
    List<Long> findchemProductSimpleList();

}
